using System;
using System.Data;
using System.Text;
using System.Collections;

namespace MSIXML
{
	/// <summary>
	/// This class allows you to convert MSI files to dataset-compatible XML files and vice-versa.
	/// </summary>
	public class Converter
	{
		private Converter()
		{
		}

		/// <summary>
		/// Builds an MSI database out of an XML dataset.
		/// </summary>
		/// <param name="inFile">
		/// The full path to the source XML file.
		/// </param>
		/// <param name="outFile">
		/// The full path to the destination MSI file.
		/// </param>
		public static void XMLToMSI(string inFile, string outFile)
		{
			// collection of temporary file names, to delete when done
			System.Collections.Specialized.StringCollection tempFiles;
			tempFiles = new System.Collections.Specialized.StringCollection();
			
			// handles used
			IntPtr handle = IntPtr.Zero;
			IntPtr view = IntPtr.Zero;
			IntPtr rec = IntPtr.Zero;
			
			// read XML file into dataset
			System.Data.DataSet dataSet1 = new DataSet();
			dataSet1.CaseSensitive = true;
			dataSet1.ReadXml(inFile, System.Data.XmlReadMode.ReadSchema);
			
			// remove output file if it already exists
			// this ensures that a new MSI file is created
			try
			{
				if(System.IO.File.Exists(outFile))
					System.IO.File.Delete(outFile);
			}
			catch(Exception){}

			// create and save a blank database
			// this ensures that the real database will be as small as possible
			API.MsiOpenDatabase(outFile, API.MSIDBOPEN_CREATEDIRECT, ref handle);
			API.MsiDatabaseCommit(handle);
			API.MsiCloseHandle(handle);

			// delete output file again
			try
			{
				System.IO.File.Delete(outFile);
			}
			catch(Exception){}

			// create the real database now
			// it should now be very compact
			API.MsiOpenDatabase(outFile, API.MSIDBOPEN_CREATEDIRECT, ref handle);
			
			// iterate through dataset tables
			foreach(System.Data.DataTable tbl in dataSet1.Tables)
			{
				// prepare the SQL statement for creating table
				string sql = "CREATE TABLE `" + tbl.TableName + "` (";
				int nKeys = 0;
				
				// iterate through columns
				foreach(System.Data.DataColumn clmn in tbl.Columns)
				{
					sql += "`" + clmn.ColumnName + "` ";
					
					// different types for string and char[]
					if(clmn.DataType.Equals(typeof(string)) || clmn.DataType.Equals(typeof(char[])))
					{
						// use LONGCHAR for variable character arrays
						// when MaxLength is not specified
						if(!clmn.ExtendedProperties.ContainsKey("MaxLength"))
						{
							sql += "LONGCHAR ";
						}
							// use CHAR(n) for fixed character arrays
						else
						{
							sql += "CHAR(" + clmn.ExtendedProperties["MaxLength"].ToString() + ") ";
						}
					}
						// C# short type is INT in SQL
					else if(clmn.DataType.Equals(typeof(short)))
					{
						sql += "INT ";
					}
						// C# int type is LONG in SQL
					else if(clmn.DataType.Equals(typeof(int)))
					{
						sql += "LONG ";
					}
						// byte array stream stored as BLOB
					else if(clmn.DataType.Equals(typeof(byte[])))
					{
						sql += "OBJECT ";
					}
						// for all other types, just store them as strings
					else
					{
						sql += "LONGCHAR ";
					}
					
					// special flag for columns not allowing NULLs
					if(!clmn.AllowDBNull)
					{
						sql += "NOT NULL ";
					}

					// localizable flag
					if(clmn.ExtendedProperties.ContainsKey("Localized") &&
						bool.Parse(clmn.ExtendedProperties["Localized"].ToString()) == true)
					{
						sql += "LOCALIZABLE ";
					}
					// temporary flag
					if(clmn.ExtendedProperties.ContainsKey("Temporary") &&
						bool.Parse(clmn.ExtendedProperties["Temporary"].ToString()) == true)
					{
						sql += "TEMPORARY ";
					}
					// finish it off
					sql += ", ";
					
					// count the number of key columns, used later
					if(clmn.ExtendedProperties.ContainsKey("IsKey") &&
						bool.Parse(clmn.ExtendedProperties["IsKey"].ToString()) == true)
						nKeys++;
				}
				// remove trailing comma
				sql = sql.Remove(sql.Length-2, 2);

				// check if keys have been found
				if(nKeys > 0)
				{
					// if so, add the PRIMARY KEY clause
					sql += "PRIMARY KEY ";

					// iterate through columns again
					foreach(System.Data.DataColumn ckey in tbl.Columns)
					{
						// for all key columns, add column name to SQL statement
						if(ckey.ExtendedProperties.ContainsKey("IsKey") &&
							bool.Parse(ckey.ExtendedProperties["IsKey"].ToString()) == true)
						{
							sql += "`" + ckey.ColumnName + "`, ";
						}
					}
					// remove trailing comma
					sql = sql.Remove(sql.Length-2, 2);
				}
				// finish off SQL statement
				sql += ")";

				// execute SQL statement, creating a new table
				API.MsiDatabaseOpenView(handle, sql, ref view);
				if(API.MsiViewExecute(view, IntPtr.Zero) != 0)
				{
					// output debug message if failed
					System.Diagnostics.Debug.WriteLine("Error executing view: " + sql);
				}
				// close view handle
				API.MsiViewClose(view);
				API.MsiCloseHandle(view);

				// retrieve all rows from table
				sql = "SELECT * FROM `" + tbl.TableName + "`";
				if(API.MsiDatabaseOpenView(handle, sql, ref view) != 0)
				{
					// output debug message if failed
					System.Diagnostics.Debug.WriteLine("Error opening view: " + sql);
				}
				API.MsiViewExecute(view, IntPtr.Zero);

				// iterate through dataset table rows
				foreach(System.Data.DataRow r in tbl.Rows)
				{
					// create a blank record
					rec = API.MsiCreateRecord(r.ItemArray.Length);
					API.MsiRecordClearData(rec);
					
					// iterate through data items in record
					int i = 1;
					foreach(object o in r.ItemArray)
					{
						// set value if not null
						if(o != null && o != System.DBNull.Value)
						{
							// set value for non-BLOB types
							if(!tbl.Columns[i-1].DataType.Equals(typeof(byte[])))
							{
								API.MsiRecordSetString(rec, i, o.ToString());
							}
								// set value for binary streams (BLOBs)
							else
							{
								// export stream of bytes to temporary file
								string file = System.IO.Path.GetTempFileName();
								System.IO.FileStream fs;
								fs = new System.IO.FileStream(file, System.IO.FileMode.Create,
									System.IO.FileAccess.Write, System.IO.FileShare.Write);
								fs.Write((byte[])o, 0, ((byte[])o).Length);
								fs.Close();

								// load stream from file
								API.MsiRecordSetStream(rec, i, file);
								// we need to get rid of this file later on
								tempFiles.Add(file);
							}
						}
							// set non-BLOB values to NULL otherwise
						else
						{
							if(!tbl.Columns[i-1].DataType.Equals(typeof(byte[])))
							{
								API.MsiRecordSetString(rec, i, null);
							}
						}
						i++;
					}
					// execute MODIFY query to insert new record
					if(API.MsiViewModify(view, API.MSIMODIFY_INSERT, rec) != 0)
					{
						// output error message
						System.Diagnostics.Debug.WriteLine("Error executing view");
					}
					// close record set handle
					API.MsiCloseHandle(rec);
				}
				// close view handle
				API.MsiViewClose(view);
				API.MsiCloseHandle(view);
			}
			// commit database, applying changes
			API.MsiDatabaseCommit(handle);
			// close database file
			API.MsiCloseHandle(handle);

			// remove all temporary files
			foreach(string file in tempFiles)
			{
				System.IO.File.Delete(file);
			}
		}

		private static void MSIToXML(string inFile, string outFile, string dataSetName, System.Collections.Specialized.StringCollection tables)
		{
			// create a blank dataset
			System.Data.DataSet dataSet1 = new DataSet(dataSetName);
			dataSet1.CaseSensitive = true;
			
			// handles to be used later on
			IntPtr handle = IntPtr.Zero;
			IntPtr view = IntPtr.Zero;
			IntPtr rec = IntPtr.Zero;
			IntPtr view2 = IntPtr.Zero;
			IntPtr rec2 = IntPtr.Zero;
			
			// opens the source database in read-only direct mode
			API.MsiOpenDatabase(inFile,
				API.MSIDBOPEN_READONLY, ref handle);
			
			// get a list of tables
			API.MsiDatabaseOpenView(handle, "SELECT Name FROM _Tables", ref view);
			API.MsiViewExecute(view, IntPtr.Zero);

			// iterate through the list of tables
			int result = 0;
			while((result = API.MsiViewFetch(view, ref rec)) != API.ERROR_NO_MORE_ITEMS)
			{
				// exit when an error occurs
				if(result != 0)
					break;

				// retrieve the table name
				StringBuilder buf = new StringBuilder();
				int len = 0;
				// first try, get length of string
				API.MsiRecordGetString(rec, 1, buf, ref len);
				len++;
				buf = new StringBuilder(len);
				// second try, get string itself
				API.MsiRecordGetString(rec, 1, buf, ref len);
				
				// add the table to dataset
				if(tables == null ||
					tables.Contains(buf.ToString()))
				{
					dataSet1.Tables.Add(buf.ToString());
				}
			}

			// close record set
			API.MsiCloseHandle(rec);
			
			// add storage and streams tables
			if(tables == null ||
				tables.Contains("_Storages"))
				dataSet1.Tables.Add("_Storages");
			if(tables == null ||
				tables.Contains("_Streams"))
				dataSet1.Tables.Add("_Streams");

			// close handle to this query
			API.MsiViewClose(view);
			API.MsiCloseHandle(view);

			// iterate through tables
			foreach(DataTable tbl in dataSet1.Tables)
			{
				// pre-populate storage and stream tables
				if(tbl.TableName.Equals("_Storages") || tbl.TableName.Equals("_Streams"))
				{
					// need two columns: Name, and Data
					System.Data.DataColumn clm1 = tbl.Columns.Add("Name", typeof(string));
					System.Data.DataColumn clm2 = tbl.Columns.Add("Data", typeof(byte[]));
					// Name does not allow Null and is a primary key
					clm1.AllowDBNull = false;
					clm1.ExtendedProperties.Add("IsKey", true);
					// Data allows Null and is not a key
					clm2.AllowDBNull = true;
					// _Storages has maximum Name length of 31
					// whilst _Streams has maximum Name length of 62
					if(tbl.TableName.Equals("_Storages"))
						clm1.ExtendedProperties.Add("MaxLength", 31);
					else
						clm1.ExtendedProperties.Add("MaxLength", 62);
				}

				// retrieve column names
				API.MsiDatabaseOpenView(handle, "SELECT Number,Name FROM _Columns " +
					"WHERE `Table`='" + tbl.TableName + "' ORDER BY `Number`", ref view);
				API.MsiViewExecute(view, IntPtr.Zero);
				
				// iterate through retrieved column names
				while((result = API.MsiViewFetch(view, ref rec)) != API.ERROR_NO_MORE_ITEMS)
				{
					// exit when an error occurs
					if(result != 0)
						break;

					// retrieve the column name
					StringBuilder buf = new StringBuilder();
					int len = 0;
					// first try, get length of string
					API.MsiRecordGetString(rec, 2, buf, ref len);
					len++;
					buf = new StringBuilder(len);
					// second try, get string itself
					API.MsiRecordGetString(rec, 2, buf, ref len);
					
					// add column with corresponding name
					System.Data.DataColumn clmn = tbl.Columns.Add(buf.ToString());
					
					// retrieve all rows from this table
					API.MsiDatabaseOpenView(handle, "SELECT * FROM " + tbl.TableName, ref view2);
					API.MsiViewExecute(view2, IntPtr.Zero);
					
					// find column types
					API.MsiViewGetColumnInfo(view2, API.MSICOLINFO_TYPES, ref rec2);
					API.MsiViewClose(view2);
					API.MsiCloseHandle(view2);
					
					// current column's type
					int col = API.MsiRecordGetInteger(rec, 1);
					buf = new StringBuilder();
					len = 0;
					// first try, get length of string
					API.MsiRecordGetString(rec2, col, buf, ref len);
					len++;
					buf = new StringBuilder(len);
					// second try, get string itself
					API.MsiRecordGetString(rec2, col, buf, ref len);

					API.MsiCloseHandle(rec2);
					
					// upper-case allows Null, lower-case does not
					if(buf.ToString()[0] >= 'A' && buf.ToString()[0] <= 'Z')
						clmn.AllowDBNull = true;
					else
						clmn.AllowDBNull = false;

					// convert buffer to lower-case now
					buf = new StringBuilder(buf.ToString().ToLower());

					// localized attribute
					if(buf.ToString().StartsWith("l"))
					{
						clmn.ExtendedProperties.Add("Localized", true);
					}
					// temporary attribute
					if(buf.ToString().StartsWith("g") || buf.ToString().StartsWith("j"))
					{
						clmn.ExtendedProperties.Add("Temporary", true);
					}

					// string data type, no bounds
					if(buf.ToString().StartsWith("s0") || buf.ToString().StartsWith("l0") || buf.ToString().StartsWith("g0"))
					{
						clmn.DataType = typeof(string);
					}
						// string data type, with bounds
					else if((buf.ToString().StartsWith("s") || buf.ToString().StartsWith("l") || buf.ToString().StartsWith("g")) && buf.ToString().IndexOf(" ") == -1)
					{
						clmn.DataType = typeof(string);
						clmn.ExtendedProperties.Add("MaxLength", int.Parse(buf.ToString().Replace("s", "").Replace("l", "")));
					}
						// alternate string data type, with bounds
					else if((buf.ToString().StartsWith("s") || buf.ToString().StartsWith("l") || buf.ToString().StartsWith("g")) && buf.ToString().IndexOf(" ") != -1)
					{
						clmn.DataType = typeof(char[]);
						clmn.ExtendedProperties.Add("MaxLength", int.Parse(buf.ToString().Replace(" ", "").Replace("l", "").Replace("s", "")));
					}
						// short integers
					else if(buf.ToString().StartsWith("i2") || buf.ToString().StartsWith("j2"))
					{
						clmn.DataType = typeof(short);
					}
						// long integers
					else if(buf.ToString().StartsWith("i4") || buf.ToString().StartsWith("j4"))
					{
						clmn.DataType = typeof(int);
					}
						// binary stream
					else if(buf.ToString().StartsWith("v"))
					{
						clmn.DataType = typeof(byte[]);
					}
						// unknown type
					else
					{
						clmn.DataType = typeof(object);
					}
					// close record set
					API.MsiCloseHandle(rec);
				}

				// close handle to the query
				API.MsiViewClose(view);
				API.MsiCloseHandle(view);

				// find primary key columns
				API.MsiDatabaseGetPrimaryKeys(handle, tbl.TableName, ref rec);
				int fieldCnt = API.MsiRecordGetFieldCount(rec);
				for(int i = 1; i <= fieldCnt; i++)
				{
					// retrieve column name
					StringBuilder buf = new StringBuilder();
					int len = 0;
					// first try, get length of string
					API.MsiRecordGetString(rec, i, buf, ref len);
					len++;
					buf = new StringBuilder(len);
					// second try, get string itself
					API.MsiRecordGetString(rec, i, buf, ref len);

					// iterate through columns, setting IsKey property
					foreach(System.Data.DataColumn clmn in tbl.Columns)
					{
						if(clmn.ColumnName.Equals(buf.ToString()))
							clmn.ExtendedProperties.Add("IsKey", true);
					}
				}
				
				// close record set
				API.MsiCloseHandle(rec);

				// select data, so we can populate table with records (rows)
				API.MsiDatabaseOpenView(handle, "SELECT * FROM `" + tbl.TableName + "`", ref view);
				API.MsiViewExecute(view, IntPtr.Zero);
				
				// iterate through retrieved rows
				while((result = API.MsiViewFetch(view, ref rec)) != API.ERROR_NO_MORE_ITEMS)
				{
					// exit when error encountered
					if(result != 0)
						break;

					// iterate through fields
					fieldCnt = API.MsiRecordGetFieldCount(rec);
					// create an array of data values
					object[] data = new object[fieldCnt];
					for(int col = 1; col <= fieldCnt; col++)
					{
						// skip if null
						if(API.MsiRecordIsNull(rec, col))
							continue;

						// obtain column and data type
						System.Data.DataColumn clmn = tbl.Columns[col-1];
						data[col-1] = null;
						
						// retrieve as integer
						if(clmn.DataType.Equals(typeof(int)) || clmn.DataType.Equals(typeof(short)))
						{
							// assign data value
							data[col-1] = API.MsiRecordGetInteger(rec, col);
						}
							// retrieve as string
						else if(!clmn.DataType.Equals(typeof(byte[])))
						{
							StringBuilder buf = new StringBuilder();
							int len = 0;
							// first try, get length of string
							API.MsiRecordGetString(rec, col, buf, ref len);
							len++;
							buf = new StringBuilder(len);
							// second try, get string itself
							API.MsiRecordGetString(rec, col, buf, ref len);
							
							// assign data value
							data[col-1] = buf.ToString();
						}
							// read a stream
						else
						{
							// master array list for storing bytes of stream
							System.Collections.ArrayList master = new ArrayList();
							// a very tiny, and very slow buffer
							byte[] buf = new byte[1];
							int len = 1;
							// read stream as long as there are bytes available
							while(len == 1)
							{
								// read stream, one byte at a time
								API.MsiRecordReadStream(rec, col, buf, ref len);
								// exit if no more data available
								if(len == 0)
									break;
								// add byte to master array
								master.Add(buf[0]);
							}
							// create an array buffer containing all bytes from stream
							byte[] masterBuf = new byte[master.Count];
							// copy master array list to the array
							int i = 0;
							foreach(byte b in master)
							{
								masterBuf[i] = b;
								i++;
							}
							// assign data value
							data[col-1] = masterBuf;
						}
					}
					// add row to table
					tbl.Rows.Add(data);
					API.MsiCloseHandle(rec);
				}
				// close handles
				API.MsiViewClose(view);
				API.MsiCloseHandle(view);
			}

			// close MSI database
			API.MsiCloseHandle(handle);
			
			// serialize dataset to XML file, writing schema as well
			dataSet1.WriteXml(outFile, XmlWriteMode.WriteSchema);
		}

		/// <summary>
		/// Dumps the contents of any MSI database to an XML dataset.
		/// </summary>
		/// <param name="inFile">
		/// The full path to the source MSI file.
		/// </param>
		/// <param name="outFile">
		/// The full path to the destination XML file.
		/// </param>
		/// <param name="dataSetName">
		/// The name of the dataset generated.
		/// </param>
		public static void MSIToXML(string inFile, string outFile, string dataSetName)
		{
			MSIToXML(inFile, outFile, dataSetName, (System.Collections.Specialized.StringCollection)null);
		}
		/// <summary>
		/// Dumps the contents of any MSI database to an XML dataset.
		/// Only extracts the specified tables.
		/// </summary>
		/// <param name="inFile">
		/// The full path to the source MSI file.
		/// </param>
		/// <param name="outFile">
		/// The full path to the destination XML file.
		/// </param>
		/// <param name="tables">
		/// The name(s) of the table(s) to extract.
		/// </param>
		/// <param name="dataSetName">
		/// The name of the dataset generated.
		/// </param>
		public static void MSIToXML(string inFile, string outFile, string dataSetName, params string[] tables)
		{
			System.Collections.Specialized.StringCollection tablesCol;
			tablesCol = new System.Collections.Specialized.StringCollection();
			tablesCol.AddRange(tables);
			MSIToXML(inFile, outFile, dataSetName, tablesCol);
		}
	}
}
